import pandas as pd
from functools import reduce
from iotdb.Session import Session
import templates

# 按月份，项目人数top10上传到mysql
com_json=templates.DATA_JSON()
db_mysql=templates.DB_MYSQL()

ip = "127.0.0.1"
port_ = "6667"
username_ = "root"
password_ = "root"
session = Session(ip, port_, username_, password_)
session.open()

class OPEN_MYSQL:
    # 执行sql
    def list_sql(self, combined_list):
        db_mysql.Connect()
        db_mysql.cursor = db_mysql.con.cursor()
        try:
            sql = "INSERT IGNORE INTO item_people (times,people_top10) VALUES (%s,%s)"
            # 执行sql命令
            db_mysql.cursor.executemany(sql, combined_list)
            # print("执行成功")
        except Exception as e:
            print(e)
        finally:
            # 提交
            db_mysql.con.commit()
            db_mysql.cursor.close()
            db_mysql.con.close()


dfs = []
for i in range(100):
    table, goal = com_json.dir_json(i)
    data = session.execute_query_statement(
        f"select participants from root.opendigger.{table} where time >=2019-01-01T00:00:00.000+08:00")
    df = data.todf()
    dfs.append(df)

# 使用 reduce 函数批量合并 DataFrame
df = reduce(lambda left, right: pd.merge(left, right, on='Time'), dfs)

# 将 Time 列从时间戳转换为年月日格式
df['Time'] = (df['Time'] / 1000 + 86400).astype(int)
df['Time'] = pd.to_datetime(df['Time'], unit='s').dt.strftime('%Y-%m-%d')

# 去除所有列名中的 'participants' 字符串
df.columns = df.columns.str.replace(r'(\.participants|root\.opendigger\.)', '', regex=True)

# 存储最终的 JSON 结果
result = []

# 遍历每一行数据
for index, row in df.iterrows():
    # 排除 Time 列，获取其他列的数值
    row_data = row.drop('Time')

    # 将数值按从大到小排序
    sorted_data = row_data.sort_values(ascending=False)

    # 获取前10个最大的数和它们对应的列名
    top_2 = sorted_data.head(10)

    # 创建一个字典用于保存结果
    result_dict = {row['Time']: top_2.to_dict()}

    result.append(result_dict)

model = OPEN_MYSQL()
data_list = []
conbine_list = []
for i in range(len(result)):
    data_list = []
    date = result[i].keys()
    data = result[i].values()

    data_list.append(str(list(date)[0]))
    data_list.append(str(list(data)[0]))
    conbine_list.append(data_list)

model.list_sql(conbine_list)

session.close()


